#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto

#改写上述的模板 练习一下如何使用shell执行presto sql

/export/server/presto/bin/presto --catalog hive --server hadoop01:8090 --execute "
-- 旷课人数
INSERT INTO edu_dm.dm_absent_result
SELECT
    class_date,d
    sc.class_id AS class_id,
    morning_absent_count,
    concat(cast(morning_absent_count * 1.00 / studying_student_count * 100 as varchar), '%') AS morning_absent_rate,
    afternoon_absent_count,
    concat(cast(afternoon_absent_count * 1.00 / studying_student_count * 100 as varchar), '%')AS afternoon_absent_ate,
    evening_absent_count,
    concat(cast(evening_absent_count * 1.00 / studying_student_count * 100 as varchar), '%') AS evening_absent_rate
FROM edu_dta.dta_absent_count AS sc
JOIN edu_dwd.dwd_dt_class_studying_student_count AS cssc
ON sc.class_id = cssc.class_id AND sc.class_date = cssc.studying_date;


INSERT INTO edu_dm.dm_signin_result
SELECT
    class_date,
    sc.class_id AS class_id,
    morning_signin_count,
    concat(cast(morning_signin_count * 1.00 / studying_student_count * 100 as varchar) , '%') AS morning_signin_date,
    afternoon_signin_count,
    concat(cast(afternoon_signin_count * 1.00 / studying_student_count * 100 as varchar), '%') AS afternoon_signin_rate,
    evening_signin_count,
    concat(cast(afternoon_signin_count * 1.00 / studying_student_count * 100 as varchar), '%') AS evening_signin_rate
FROM edu_dta.dta_signin_count AS sc
JOIN edu_dwd.dwd_dt_class_studying_student_count AS cssc
ON sc.class_id = cssc.class_id AND sc.class_date = cssc.studying_date;


INSERT INTO edu_dm.dm_late_result
SELECT
    class_date,
    sc.class_id AS class_id,
    morning_late_count,
    concat(cast(morning_late_count * 1.00 / studying_student_count * 100 as varchar), '%') AS morning_late_rate,
    afternoon_late_count,
    concat(cast(afternoon_late_count * 1.00 / studying_student_count * 100 as varchar), '%') AS afternoon_late_rate,
    evening_late_count,
    concat(cast(evening_late_count * 1.00 / studying_student_count * 100 as varchar), '%') AS evening_late_rate
FROM edu_dta.dta_signin_count AS sc
JOIN edu_dwd.dwd_dt_class_studying_student_count AS cssc
ON sc.class_id = cssc.class_id AND sc.class_date = cssc.studying_date;


INSERT INTO edu_dm.dm_leave_result
SELECT
    class_date,
    sc.class_id AS class_id,
    morning_leave_count,
    concat(cast(morning_leave_count * 1.00 / studying_student_count * 100 as varchar), '%') AS morning_leave_rate,
    afternoon_leave_count,
    concat(cast(afternoon_leave_count * 1.00 / studying_student_count * 100 as varchar), '%') AS afternoon_leave_rate,
    evening_leave_count,
    concat(cast(evening_leave_count * 1.00 / studying_student_count * 100 as varchar), '%') AS evening_leave_rate
FROM edu_dta.dta_leave_count AS sc
JOIN edu_dwd.dwd_dt_class_studying_student_count AS cssc
ON sc.class_id = cssc.class_id AND sc.class_date = cssc.studying_date;


INSERT INTO edu_dm.dm_late_result
SELECT
    class_date,
    sc.class_id AS class_id,
    morning_late_count,
    concat(cast(morning_late_count * 1.00 / studying_student_count * 100 as varchar), '%') AS morning_late_rate,
    afternoon_late_count,
    concat(cast(afternoon_late_count * 1.00 / studying_student_count * 100 as varchar), '%') AS afternoon_late_rate,
    evening_late_count,
    concat(cast(evening_late_count * 1.00 / studying_student_count * 100 as varchar), '%') AS evening_late_rate
FROM edu_dta.dta_signin_count AS sc
JOIN edu_dwd.dwd_dt_class_studying_student_count AS cssc
ON sc.class_id = cssc.class_id AND sc.class_date = cssc.studying_date;
"